package edu.ubb.scp.dao.jdbc;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import edu.ubb.scp.dao.BaseDAO;
import edu.ubb.scp.exception.DAOException;
import edu.ubb.scp.exception.UnsafeException;
import edu.ubb.scp.model.Model;
import edu.ubb.scp.model.Order;
import edu.ubb.scp.model.Product;

public class BaseJdbcDAO implements BaseDAO {

	@Override
	public void insert(Model model) {

		// init class information
		Class<? extends Model> c = model.getClass();
		Field[] fields = c.getDeclaredFields();
		Field index = model.getPrimaryKey();

		// build insert string
		StringBuilder command = new StringBuilder("INSERT INTO `"
				+ c.getSimpleName() + "` (");
		StringBuilder end = new StringBuilder();
		int n = 0;
		for (Field f : fields) {
			if (!f.equals(index)) {
				if (n > 0) {
					command.append(", ");
					end.append(", ");
				}
				command.append("`" + f.getName() + "`");
				end.append("?");
				++n;
			}
		}
		command.append(") VALUES (");
		command.append(end);
		command.append(")");
		System.out.println(command.toString());

		// execute insert
		try {
			PreparedStatement statement = JdbcConnection.getConnection()
					.prepareStatement(command.toString(),
							Statement.RETURN_GENERATED_KEYS);

			n = 1;
			for (Field f : fields) {
				if (!f.equals(index)) {
					f.setAccessible(true);
					if (f.getType().equals(int.class)) {
						statement.setInt(n, f.getInt(model));
					} else if (f.getType().equals(float.class)) {
						statement.setFloat(n, f.getFloat(model));
					} else {
						statement.setString(n, f.get(model).toString());
					}
					f.setAccessible(false);
					++n;
				}
			}
			statement.executeUpdate();
			if (index != null) {
				ResultSet result = statement.getGeneratedKeys();
				result.next();
				index.setAccessible(true);
				index.setInt(model, result.getInt(1));
				index.setAccessible(false);
			}
		} catch (SQLException | IllegalArgumentException
				| IllegalAccessException e) {
			e.printStackTrace();
		}
	}

	/**
	 * deletes model from database
	 */
	@Override
	public void delete(Model model) {
		// init class information
		Class<? extends Model> c = model.getClass();
		List<Field> indexes = Arrays.asList(model.getKeys());

		// build insert string
		StringBuilder command = new StringBuilder("DELETE FROM `"
				+ c.getSimpleName() + "` WHERE ");
		int n = 0;
		for (Field f : indexes) {
			if (n > 0)
				command.append(" AND ");
			command.append("`" + f.getName() + "` = ?");
			++n;
		}

		// execute delete
		try {
			PreparedStatement statement = JdbcConnection.getConnection()
					.prepareStatement(command.toString());

			n = 1;
			for (Field f : indexes) {
				f.setAccessible(true);
				if (f.getType().equals(int.class)) {
					statement.setInt(n, f.getInt(model));
				} else if (f.getType().equals(float.class)) {
					statement.setFloat(n, f.getFloat(model));
				} else {
					statement.setString(n, f.get(model).toString());
				}
				f.setAccessible(false);
				++n;
			}
			statement.executeUpdate();
		} catch (SQLException | IllegalArgumentException
				| IllegalAccessException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void update(Model model) {
		// init class information
		Class<? extends Model> c = model.getClass();
		Field[] fields = c.getDeclaredFields();
		List<Field> indexes = Arrays.asList(model.getKeys());

		// build insert string
		StringBuilder command = new StringBuilder("UPDATE `"
				+ c.getSimpleName() + "` SET ");
		int n = 0;
		for (Field f : fields) {
			if (!indexes.contains(f)) {
				if (n > 0)
					command.append(", ");
				command.append("`" + f.getName() + "` = ?");
				++n;
			}
		}
		command.append(" WHERE ");
		n = 0;
		for (Field f : indexes) {
			if (n > 0)
				command.append(" AND ");
			command.append("`" + f.getName() + "` = ?");
			++n;
		}

		// execute update
		try {
			PreparedStatement statement = JdbcConnection.getConnection()
					.prepareStatement(command.toString());

			n = 1;
			for (Field f : fields) {
				if (!indexes.contains(f)) {
					f.setAccessible(true);
					if (f.getType().equals(int.class)) {
						statement.setInt(n, f.getInt(model));
					} else if (f.getType().equals(float.class)) {
						statement.setFloat(n, f.getFloat(model));
					} else {
						statement.setString(n, f.get(model).toString());
					}
					f.setAccessible(false);
					++n;
				}
			}

			for (Field f : indexes) {
				f.setAccessible(true);
				if (f.getType().equals(int.class)) {
					statement.setInt(n, f.getInt(model));
				} else if (f.getType().equals(float.class)) {
					statement.setFloat(n, f.getFloat(model));
				} else {
					statement.setString(n, f.get(model).toString());
				}
				f.setAccessible(false);
				++n;
			}
			statement.executeUpdate();
		} catch (SQLException | IllegalArgumentException
				| IllegalAccessException e) {
			e.printStackTrace();
		}
	}

	@Override
	public ArrayList<Model> select(Class<? extends Model> model, String where) {
		ArrayList<Model> models = new ArrayList<Model>();
		String command = "SELECT * FROM `" + model.getSimpleName() + "` WHERE "
				+ where;
		PreparedStatement statement;
		try {
			statement = JdbcConnection.getConnection()
					.prepareStatement(command);
			ResultSet result = statement.executeQuery();
			while (result.next()) {
				models.add(getModelFromResult(model, result));
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return models;
	}

	@Override
	public ArrayList<Model> select(Class<? extends Model> model) {
		ArrayList<Model> models = new ArrayList<Model>();
		String command = "SELECT * FROM `" + model.getSimpleName();
		PreparedStatement statement;
		try {
			statement = JdbcConnection.getConnection()
					.prepareStatement(command);
			ResultSet result = statement.executeQuery();
			while (result.next()) {
				models.add(getModelFromResult(model, result));
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return models;
	}

	private Model getModelFromResult(Class<? extends Model> model,
			ResultSet result) throws SQLException, InstantiationException,
			IllegalAccessException {
		Model m = model.newInstance();
		for (Field f : model.getDeclaredFields()) {
			f.setAccessible(true);
			if (f.getType().equals(int.class)) {
				f.setInt(m, result.getInt(f.getName()));
			} else if (f.getType().equals(float.class)) {
				f.setFloat(m, result.getFloat(f.getName()));
			} else {
				f.set(m, result.getString(f.getName()));
			}
			f.setAccessible(false);
		}
		return m;
	}

	@Override
	public void insertSafe(Order model) throws UnsafeException {
		Connection conn = null;
		Savepoint save = null;
		try {
			conn = JdbcConnection.getConnection();
			conn.setAutoCommit(false);
			save = conn.setSavepoint();
		} catch (SQLException e) {
			throw new UnsafeException();
		}
		try {
			Product product = (Product) select(Product.class,
					"`ProductID` = " + model.getProductID()).get(0);
			if ((product == null) || (product.getStock() < model.getQuantity())) {
				conn.rollback(save);
				conn.setAutoCommit(true);
				throw new UnsafeException();
			}
			product.setStock(product.getStock() - model.getQuantity());
			update(product);
			insert(model);
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e1) {
			try {
				conn.rollback(save);
				conn.setAutoCommit(true);
			} catch (SQLException e) {
			}
			throw new UnsafeException();
		}
	}
}
